package com.victor.core.util;

import jxl.write.Number;
import jxl.write.WritableWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 用来输出Excel表格，需要jxl.jar 通过这个类，只需要设置报表标题，字段名称及标签，就可以把一组对象导出Excel表
 */
@SuppressWarnings({"rawtypes"})
public class ExcelReport {
    private final static Logger logger = LoggerFactory.getLogger(ExcelReport.class);
    private String title;
    private String[] lables;
    private String[] fields;
    private Map<String, String> lockMap;
    private List list;
    private java.io.OutputStream out;
    private HttpServletResponse response;
    /**
     * excel sheet最大行数
     */
    public static final int MAX_ROW = 65536;

    public ExcelReport(java.io.OutputStream out, String title, String[] lables, String[] fields,
                       Map<String, String> lockMap, List list, HttpServletResponse response) {
        this.out = out;
        this.title = title;
        this.lables = lables;
        this.fields = fields;
        this.list = list;
        this.response = response;
        this.lockMap = lockMap;
    }

    public ExcelReport(java.io.OutputStream out, String[] fields, Map<String, String> lockMap, List list) {
        this(out, "", fields, fields, lockMap, list, null);
    }

    public ExcelReport(String title, String[] lables, String[] fields, Map<String, String> lockMap, List list,
                       HttpServletResponse response) {
        this(null, title, lables, fields, lockMap, list, response);
    }

    public ExcelReport(String title, String[] fields, Map<String, String> lockMap, List list) {
        this(null, title, fields, fields, lockMap, list, null);
    }

    public ExcelReport(List list) {
        this(null, null, null, null, null, list, null);
    }

    public void export() throws Exception {
        if (this.out == null) {
            // this.out =
            // com.easyjf.web.ActionContext.getContext().getResponse().getOutputStream();
            this.out = this.response.getOutputStream();
            this.response.setContentType("APPLICATION/OCTET-STREAM");
            this.response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + new String((this.title + ".xls").getBytes(), "iso8859-1") + "\"");
        }
        if (this.fields == null || this.fields.length < 1) {
            if (list != null && !this.list.isEmpty()) {
                Object obj = this.list.get(0);
                if (obj instanceof java.util.Map) {
                    Map m = (Map) obj;
                    java.util.Iterator it = m.keySet().iterator();
                    this.fields = new String[m.size()];
                    int i = 0;
                    while (it.hasNext()) {
                        this.fields[i++] = it.next().toString();
                    }
                } else {
                    // 获取所有字段名称
                    Field[] objFields = obj.getClass().getDeclaredFields();
                    this.fields = new String[objFields.length];
                    for (int i = 0; i < this.fields.length; i++) {
                        this.fields[i] = objFields[i].getName();
                    }
                }
            }
        }

        if (this.lables == null || this.lables.length < 1) {
            this.lables = this.fields;
        }
        if (ToolUtil.isEmpty(this.title)) {
            if (list != null && !this.list.isEmpty()) {
                Object obj = this.list.get(0);
                this.title = obj.getClass().getSimpleName();
            }
        }
        WritableWorkbook book = jxl.Workbook.createWorkbook(out);
        // 解锁
        jxl.write.WritableCellFormat formatUnlock = new jxl.write.WritableCellFormat();
        formatUnlock.setLocked(false);
        // 锁定保护单元格
        jxl.write.WritableCellFormat formatLock = new jxl.write.WritableCellFormat();
        formatLock.setLocked(true);
        int sheetNum = 1;
        jxl.write.WritableSheet sheet = book.createSheet("sheet" + (sheetNum++), 0);
        // 给sheet电子版中所有的列设置默认的列的宽度;
        sheet.getSettings().setDefaultColumnWidth(20);
        // sheet.setProtected(true);
        if (this.lables != null) {
            for (int l = 0; l < lables.length; l++) {
                // a 单元格的列号搜索 b 单元格的行号 c 从单元格[a,b]起，向下合并的列数 d 从单元格[a,b]起，向下合并的行数
                sheet.addCell(new jxl.write.Label(l, 0, lables[l]));
                sheet.getWritableCell(l, 0).setCellFormat(formatLock);
            }

            int row = 1;
            if (list != null && !list.isEmpty()) {
                for (int i = 0; i < list.size(); i++, row++) {
                    if (row > MAX_ROW - 1) {
                        row = 1;
                        sheet = book.createSheet("sheet" + (sheetNum++), 0);
                        for (int l = 0; l < lables.length; l++) {
                            sheet.addCell(new jxl.write.Label(l, 0, lables[l]));
                        }
                    }

                    Object obj = list.get(i);
                    for (int j = 0; j < this.fields.length; j++) {
                        Object v = null;
                        if (obj instanceof java.util.Map) {
                            Map m = (Map) obj;
                            v = m.get(fields[j]);
                        } else {
                            try {
                                PropertyDescriptor pd = new PropertyDescriptor(fields[j], obj.getClass());
                                Method getMethod = pd.getReadMethod();
                                v = getMethod.invoke(obj);
                            } catch (Exception e) {
                                v = "";
                            }
                        }
                        v = filter(v, fields[j]);
                        if (v != null && v instanceof Number) {
                            sheet.addCell(new XltNumber(j, row, (Number) v));
                        } else {
                            sheet.addCell(new jxl.write.Label(j, row, v == null ? "" : formate(v)));
                        }
                        if (this.lockMap == null || ToolUtil.isEmpty(this.lockMap.get(fields[j]))) {
                            sheet.getWritableCell(j, row).setCellFormat(formatUnlock);
                        }

                    }
                }
            }
        }
        book.write();
        book.close();
    }

    public void export(HttpServletRequest request) throws Exception {
        final String userAgent = request.getHeader("USER-AGENT");
        if (this.out == null) {
            // this.out =
            // com.easyjf.web.ActionContext.getContext().getResponse().getOutputStream();
            this.out = this.response.getOutputStream();
            this.response.setContentType("APPLICATION/OCTET-STREAM");
            if (userAgent.contains("Firefox") || userAgent.contains("Chrome")) {
                title = new String(title.getBytes("UTF-8"), "ISO-8859-1");
            } else {
                title = java.net.URLEncoder.encode(title, "UTF8");
            }
            this.response.setHeader("Content-Disposition", "attachment; filename=\"" + title + ".xls\"");
        }
        if (this.fields == null || this.fields.length < 1) {
            if (list != null && !this.list.isEmpty()) {
                Object obj = this.list.get(0);
                if (obj instanceof java.util.Map) {
                    Map m = (Map) obj;
                    java.util.Iterator it = m.keySet().iterator();
                    this.fields = new String[m.size()];
                    int i = 0;
                    while (it.hasNext()) {
                        this.fields[i++] = it.next().toString();
                    }
                } else {
                    // 获取所有字段名称
                    Field[] objFields = obj.getClass().getDeclaredFields();
                    this.fields = new String[objFields.length];
                    for (int i = 0; i < this.fields.length; i++) {
                        this.fields[i] = objFields[i].getName();
                    }
                }
            }
        }

        if (this.lables == null || this.lables.length < 1) {
            this.lables = this.fields;
        }
        if (ToolUtil.isEmpty(this.title)) {
            if (list != null && !this.list.isEmpty()) {
                Object obj = this.list.get(0);
                this.title = obj.getClass().getSimpleName();
            }
        }
        WritableWorkbook book = jxl.Workbook.createWorkbook(out);

        int sheetNum = 1;
        jxl.write.WritableSheet sheet = book.createSheet("sheet" + (sheetNum++), 0);
        if (this.lables != null) {
            for (int l = 0; l < lables.length; l++) {
                sheet.addCell(new jxl.write.Label(l, 0, lables[l]));
            }

            int row = 1;
            if (list != null && !list.isEmpty()) {
                for (int i = 0; i < list.size(); i++, row++) {
                    //logger.info(row + "");
                    if (row > MAX_ROW - 1) {
                        row = 1;
                        sheet = book.createSheet("sheet" + (sheetNum++), 0);
                        for (int l = 0; l < lables.length; l++) {
                            sheet.addCell(new jxl.write.Label(l, 0, lables[l]));
                        }
                    }

                    Object obj = list.get(i);
                    for (int j = 0; j < this.fields.length; j++) {
                        Object v = null;
                        if (obj instanceof java.util.Map) {
                            Map m = (Map) obj;
                            v = m.get(fields[j]);
                        } else {
                            try {
                                PropertyDescriptor pd = new PropertyDescriptor(fields[j], obj.getClass());
                                Method getMethod = pd.getReadMethod();
                                v = getMethod.invoke(obj);
                            } catch (Exception e) {
                                v = "";
                            }
                        }
                        v = filter(v, fields[j]);
                        if (v != null && v instanceof Number) {
                            sheet.addCell(new XltNumber(j, row, (Number) v));
                        } else {
                            sheet.addCell(new jxl.write.Label(j, row, v == null ? "" : formate(v)));
                        }
                    }
                }
            }
        }
        book.write();
        book.close();
    }

    public Object filter(Object v, String field) {
        return v;
    }

    public String formate(Object v) {
        if (v instanceof Boolean) {
            Boolean b = (Boolean) v;
            if (b) {
                return "是";
            } else if (!b) {
                return "否";
            } else {
                return "";
            }
        } else if (v instanceof Date) {
            SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date d = (Date) v;
            return f.format(d);
        } else {
            return v.toString();
        }
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String[] getLables() {
        return lables;
    }

    public void setLables(String[] lables) {
        this.lables = lables;
    }

    public String[] getFields() {
        return fields;
    }

    public void setFields(String[] fields) {
        this.fields = fields;
    }

    public List getList() {
        return list;
    }

    public void setList(List list) {
        this.list = list;
    }

    public java.io.OutputStream getOut() {
        return out;
    }

    public void setOut(java.io.OutputStream out) {
        this.out = out;
    }

    public HttpServletResponse getResponse() {
        return response;
    }

    public void setResponse(HttpServletResponse response) {
        this.response = response;
    }

    private class XltNumber extends jxl.write.Number {
        public XltNumber(int col, int row, Number n) {
            super(col, row, n);
        }
    }
}
